package dyyx.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import dyyx.util.JdbcUtil;
import dyyx.util.ResultSetMeta;

public class JdbcHello {

	public static void main(String[] args) throws Exception {
		
		System.out.println("hello,clickhouse NativeJdbc,"+new Date());	
		
		// Class.forName("com.github.housepower.jdbc.ClickHouseDriver");
		
		// com.github.housepower.jdbc.ClickHouseDriver
		// ru.yandex.clickhouse.ClickHouseDriver
		// 8123  
		// Port 9000 is for clickhouse-client program. You must use port 8123 for HTTP.
		// 两个 driver 同时存在 ， 匹配第一个
		String url = "jdbc:clickhouse://10.57.31.23:8123";
		// 不指定数据库 ，连接默认数据库  default
		// http://10.57.31.23:8123/?extremes=0&compress=1&user=default&database=default
		
		System.out.println("url="+url);	
		
		System.out.println("driver="+DriverManager.getDriver(url));	

		Connection connection = DriverManager.getConnection(url,"default","");

		Statement stmt = connection.createStatement();
		System.out.println("stmt="+stmt);	
		
		// show databases
		String sql = "show databases";
		// default  system
		List<String> dbs = JdbcUtil.queryStringList(connection, sql);
		System.out.println("dbs="+dbs);	
		
		
		
		System.out.println("\n\n");
		
		// select database()
		
		// Unknown function database	
			
		
		sql = "show tables";
		
		List<String> tables = JdbcUtil.queryStringList(connection, sql);
		System.out.println("tables="+tables);	
		sql = "desc testbulk";
		
		ResultSetMeta rsm = JdbcUtil.getResultSetMataData(connection, sql);
		System.out.println(rsm);
		
		
		sql = "select *  from testbulk";
		rsm = JdbcUtil.getResultSetMataData(connection, sql);
		System.out.println(rsm);
		
		
		sql = "select name as name_ ,relation328 as relation328_ from testbulk";
		rsm = JdbcUtil.getResultSetMataData(connection, sql);
		System.out.println(rsm);
		
		
		
		sql = "select toUnixTimestamp('2020-05-07 00:00:09');";
		
		Object obj = JdbcUtil.queryAsObject(connection, sql);
		System.out.println("sql="+sql+",result="+obj+","+obj.getClass());
		
		// java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.String
		obj = JdbcUtil.queryAsString(connection, sql);
		System.out.println("sql="+sql+",result="+obj+","+obj.getClass());
		

		sql = "select toDateTime(1543075202);";
		obj = JdbcUtil.queryAsObject(connection, sql);
		System.out.println("sql="+sql+",result="+obj+","+obj.getClass());
		// native jdbc 报错
		// java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.lang.String
        // 官方jdbc ok
		obj = JdbcUtil.queryAsString(connection, sql);
		System.out.println("sql="+sql+",result="+obj+","+obj.getClass());
		
		// Exception in thread "main"
		// com.github.housepower.jdbc.ClickHouseSQLException:
		// DB::ExceptionDB::Exception: Access to file denied:
		// /data/clickhouse/data/system. Stack trace:
		// native 和 官方 都报错  
		sql = "CREATE TABLE test02( id UInt32,col1 String,col2 String,create_date date ) ENGINE = MergeTree(create_date, (id), 8192);";
		try{
		    stmt.executeUpdate(sql);
		}catch(Throwable e){
			System.out.println("create table error,"+e);

		}
		
		sql = "select * from system.databases";
		List list = JdbcUtil.queryList(connection, sql);
		for(Object item:list){
			System.out.println(item);
		}
		
		sql = "select distinct name from system.databases";
		dbs = JdbcUtil.queryStringList(connection, sql);
		// default, pass, system
		System.out.println("dbs="+dbs);	

		

		// 使用 system db
		System.out.println("\n\n use db,system");

		sql = "use system";
		// http://10.57.31.23:8123/?extremes=0&compress=1&user=default&database=default
		// There is no session
		System.out.println(stmt.execute(sql));
		
		
		sql = "show tables";
		ResultSet rs = stmt.executeQuery(sql) ;  
		
		while(rs.next()){
			System.out.println(rs.getString(1));
		}
		
		
		
		
		
		
		// rs.close();
		// stmt.close();
		// connection.close();
		
		    
		
	}
}
